package com.google.code.netbuilder.dal.providersimpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.google.code.netbuilder.dal.entities.Cable;
import com.google.code.netbuilder.dal.entities.Port;
import com.google.code.netbuilder.dal.providers.ICableProvider;
import com.google.code.netbuilder.dal.services.JDBCServise;

public class CabelProviderJDBC implements ICableProvider {

	@Override
	public void save(Cable entity) throws RuntimeException {
		String query = "INSERT INTO cable(id, type, isbroken, number_of_wires, date)" +
				" VALUES (?,?,?,?,?);";
		Connection con = null;
		PreparedStatement pst = null;
		try
		{
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			int rs=-1;
			pst = con.prepareStatement(query);
			pst.setInt(1, entity.getId());
			pst.setString(2, entity.getType());
			pst.setInt(3, entity.getIsBroken());
			pst.setInt(4, entity.getNumberOfWires());
			pst.setDate(5, new Date(entity.getDate().getTime()));
			rs = pst.executeUpdate();
			if (rs<=0) throw new SQLException("Cable didn't INSERT");
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}

	}

	@Override
	public void update(Cable entity) throws RuntimeException {
		String query = "UPDATE cable SET type=?, isbroken=?, number_of_wires=?, date=? WHERE id=?;";
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query);
			pst.setString(1, entity.getType());
			pst.setInt(2, entity.getIsBroken());
			pst.setInt(3, entity.getNumberOfWires());
			pst.setDate(4, new Date(entity.getDate().getTime()));
			pst.setInt(5, entity.getId());			
			int rs = pst.executeUpdate();		
			if (rs <= 0) throw new SQLException("Cable didn't update");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public void delete(Cable entity) throws RuntimeException {
		String query1 = "DELETE FROM cable_wire WHERE id_cable=?;";
		String query2 = "DELETE FROM cable WHERE id=?;";
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query1);
			pst.setInt(1, entity.getId());		
			int rs = pst.executeUpdate();
			pst = con.prepareStatement(query2);
			pst.setInt(1, entity.getId());		
			rs = pst.executeUpdate();	
			if (rs <= 0) throw new SQLException("Cable didn't DELETE");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}		
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public List findAll() throws RuntimeException{
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<Cable> cables = null;
		
		try {
			con = JDBCServise.getConnection();
        
			pst = con.prepareStatement("Select * From cable");
			rs = pst.executeQuery();
        
			cables = new ArrayList<Cable>();
        
			while (rs.next()) {
				Cable cable = new Cable();
				cable.setId(rs.getInt("id"));
				cable.setIsBroken(rs.getInt("isBroken"));
				cable.setNumberOfWires(rs.getInt("number_of_wires"));
				cable.setType(rs.getString("type"));
				cable.setDate(rs.getDate("date").getTime());
				cables.add(cable);
			}
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}	
		
        return cables;
	}

	@Override
	public Cable findByID(Integer id) throws RuntimeException{
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Cable cable = null;
		
		try {
			con = JDBCServise.getConnection();
        
			pst = con.prepareStatement("SELECT * FROM Cable where id ="
										+ id.toString());
			rs = pst.executeQuery();
        
			if (!rs.first()) throw new SQLException("Cable not found");
        			
			cable = new Cable();
			cable.setId(rs.getInt("id"));
			cable.setIsBroken(rs.getInt("isbroken"));
			cable.setNumberOfWires(rs.getInt("number_of_wires"));
			cable.setType(rs.getString("type"));
			cable.setDate(rs.getDate("date").getTime());
		
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}	
		
        return cable;
	}

}
